<?php

class _Am_Record_JustForPreload extends Am_Record
{
    
}

/**
 * Class represents records from table invoice - a subject to bill customer
 * Sample usage:
 * <code>
 * $b = $this->getDi()->invoiceRecord;
 * $b->add(Am_Di::getInstance()->productTable->load(1), 1);
 * $b->add(Am_Di::getInstance()->productTable->load(2), 2);
 * $b->add(Am_Di::getInstance()->productTable->load(3), 3);
 * $b->setUser(Am_Di::getInstance()->userTable->load(1445));
 * $b->setCouponCode('SECOND');
 * $errors = $b->validate();
 * if (!$errors)
 *    $b->calculate();
 * else
 *     echo($errors, 'errors');
 * </code>
 * 
 * {autogenerated}
 * @property int $invoice_id 
 * @property int $user_id 
 * @property string $paysys_id 
 * @property string $currency 
 * @property double $first_subtotal 
 * @property double $first_discount 
 * @property double $first_tax 
 * @property double $first_shipping 
 * @property double $first_total 
 * @property string $first_period 
 * @property int $rebill_times 
 * @property double $second_subtotal 
 * @property double $second_discount 
 * @property double $second_tax 
 * @property double $second_shipping 
 * @property double $second_total 
 * @property string $second_period 
 * @property double $tax_rate 
 * @property int $tax_type 
 * @property string $tax_title 
 * @property int $status 
 * @property int $coupon_id 
 * @property int $is_confirmed 
 * @property datetime $is_cancelled 
 * @property string $public_id 
 * @property string $invoice_key 
 * @property datetime $tm_added 
 * @property datetime $tm_started 
 * @property datetime $tm_cancelled 
 * @property date $rebill_date 
 * @property string $comment 
 * @property double $base_currency_multi 
 * @see Am_Table
 * @package Am_Invoice
 */
class Invoice extends Am_Record_WithData
{
    const PENDING = 0; // pending, not processed yet - initial status
    const PAID = 1; // paid and not-recurring
    const RECURRING_ACTIVE=2; // active recurring - there will be rebills, access open
    const RECURRING_CANCELLED=3; // recurring cancelled, access is open until paid
    const RECURRING_FAILED=4; // rebilling failed, access is closed
    const RECURRING_FINISHED=5; // rebilling finished, no access anymore
    const CHARGEBACK=7; // chargeback processed, no access
    const NOT_CONFIRMED = 8;
    const IS_CONFIRMED_CONFIRMED = 1;
    const IS_CONFIRMED_NOT_CONFIRMED = 0;
    const IS_CONFIRMED_WAIT_FOR_USER= -1;
    
    
    static $statusText = array(
        self::PENDING => "Pending",
        self::PAID    => "Paid", 
        self::RECURRING_ACTIVE => "Recurring Active",
        self::RECURRING_CANCELLED => "Recurring Cancelled",
        self::RECURRING_FAILED => "Recurring Failed",
        self::RECURRING_FINISHED => "Recurring Finished",
        self::CHARGEBACK => "Chargeback Received",
        self::NOT_CONFIRMED => 'Not Approved'
    );

    /**
     * Lazy-loaded list of items
     * use ONLY @see $this->getItems() to access
     * @var array of InvoiceItem records
     */
    private $_items = array();
    /** @var string */
    protected $_couponCode;
    /** @var User lazy-loading (private)  */
    protected $_user;
    /** @var _coupon lazy-loading
     *  @access private */
    protected $_coupon;
    
    
    const SAVED_TRANSACTION_KEY = '_saved_transaction';

    public function init()
    {
        parent::init();
        if (empty($this->currency))
            $this->currency = Am_Currency::getDefault();
    }

    
    /**
     * Approve Invoice. Will reprocess all saved transactions.
     * 
     * @return boolean 
     */
    public function approve(){
        // Make sure that all necessary payment plugins are loaded at this point. 
        $this->getDi()->plugins_payment->loadEnabled();
        if($this->isConfirmed()) return true;
        $old_status = $this->is_confirmed;
        $this->is_confirmed = self::IS_CONFIRMED_CONFIRMED;
        
        $this->updateSelectedFields('is_confirmed');
        $saved = array();
        foreach($this->data()->getAll() as $k=>$v)
        {
            if(strpos($k, self::SAVED_TRANSACTION_KEY) !== false)
            {
                list(,$time, $payment_id) = explode('-', $k);
                $saved[$time] = array($payment_id, $v); 
            }
        }
        ksort($saved);
        foreach($saved as $time => $v){
            $this->addAccessPeriod($v[1], $v[0] ? $v[0] : null);
            $this->data()->set(self::SAVED_TRANSACTION_KEY.'-'.$time.'-'.$v[0], null)->update();
        }
        if($old_status == self::IS_CONFIRMED_NOT_CONFIRMED) $this->sendApprovedEmail();
        return true;
    }
    
    /**
     * Create new empty InvoiceItem, assign invoice_id
     * and return
     * @return InvoiceItem
     */
    function createItem(IProduct $product = null)
    {
        $item = $this->getDi()->invoiceItemRecord;
        $item->invoice_id = @$this->invoice_id;
        if ($product)
        {
            $item->copyProductSettings($product);
        }
        return $item;
    }

    /**
     * Find an item in $this->getItems() by type and ids
     * @return InvoiceItem
     */
    function findItem($type, $id)
    {
        foreach ($this->getItems() as $item)
            if ($item->item_id == $id && $item->item_type == $type)
                return $item;
        return null;
    }

    /**
     * Delete an item
     */
    function deleteItem(InvoiceItem $item)
    {
        foreach ($this->getItems() as $k => $it)
            if ($it === $item)
                unset($this->_items[$k]);
    }

    function addItem(InvoiceItem $item)
    {
        $item->invoice_id = @$this->invoice_id;
        $this->_items[] = $item;
        return $this;
    }
    
    /**
     * @param int $num - number of item in invoice
     * @return InvoiceItem 
     */
    function getItem($num)
    {
        $i = 0;
        foreach ($this->getItems() as $item)
        {
            if ($i++ == $num) return $item;
        }
    }
    

    /**
     * @return array InvoiceItem records
     */
    function getItems()
    {
        if (!empty($this->invoice_id) && !$this->_items)
            $this->_items = $this->getDi()->invoiceItemTable->findByInvoiceId($this->invoice_id);
        return (array) $this->_items;
    }

    /**
     * return array of all items Products (it will be loaded if item_type == 'product'
     * @return array Product
     */
    function getProducts()
    {
        $ret = array();
        foreach ($this->getItems() as $item)
            if ($item->item_type == 'product')
                if ($pr = $item->tryLoadProduct())
                    $ret[] = $pr;
        return $ret;
    }

    /**
     * Add a product or calculated charge as a line to invoice
     * @throws Am_Exception_InvalidRequest if items is incompatible
     * @return Invoice provides fluent interface
     */
    function add(IProduct $product, $qty = 1)
    {
        $item = $this->findItem($product->getType(), $product->getProductId(), $product->getBillingPlanId());
        if (null == $item)
        {
            $item = $this->createItem($product);
            $error = $this->isItemCompatible($item);
            if (null != $error)
                throw new Am_Exception_InputError($error);
            $this->_items[] = $item;
        }
        if (!$item->variable_qty)
            $qty = $product->getQty(); // get default qty
        $item->add($qty);
        return $this;
    }

    /**
     * @return array of instantiated Am_Invoice_Calc_* objects
     */
    function getCalculators()
    {
        class_exists('Am_Invoice_Calc', true);
        $tax_calculators = $this->getDi()->plugins_tax->match($this);
        $ret = array_merge(
            array(
                new Am_Invoice_Calc_Zero(),
                new Am_Invoice_Calc_Coupon(),
            ),
            $tax_calculators,
            array(
                new Am_Invoice_Calc_Shipping(),
                new Am_Invoice_Calc_Total(),
            )
        );
        $event = new Am_Event_InvoiceGetCalculators($this);
        $event->setReturn($ret);
        $this->getDi()->hook->call($event);
        return $event->getReturn();
    }

    /**
     * Refresh totals according to currently selected
     *   items, _coupon, user and so ons
     * Should be called on a fresh invoice only, because
     * it may break reporting later if called on a paid
     * invoice
     * @return Invoice provides fluent interface
     */
    function calculate()
    {
        foreach ($this->getCalculators() as $calc)
            $calc->calculate($this);
        // now summarize all items to invoice totals
        $priceFields = array(
            'first_subtotal' => null,
            'first_discount' => 'first_discount',
            'first_tax' => 'first_tax',
            'first_shipping' => 'first_shipping',
            'first_total' => 'first_total',
            'second_subtotal' => null,
            'second_discount' => 'second_discount',
            'second_tax' => 'second_tax',
            'second_shipping' => 'second_shipping',
            'second_total' => 'second_total',
        );
        foreach ($priceFields as $k => $kk)
            $this->$k = 0.0;
        foreach ($this->getItems() as $item)
        {
            $this->first_subtotal += moneyRound($item->first_price * $item->qty);
            $this->second_subtotal += moneyRound($item->second_price * $item->qty);
            foreach ($priceFields as $k => $kk)
                $this->$k += $kk ? $item->$kk : 0;
        }
        foreach ($priceFields as $k => $kk)
            $this->$k = moneyRound($this->$k);
        /// set periods, it has been checked for compatibility in @see add()
        foreach ($this->getItems() as $item)
        {
            $this->currency = $item->currency;
            if (!@$this->first_period)
                $this->first_period = $item->first_period;
            if (!@$this->second_period)
                $this->second_period = $item->second_period;
            $this->rebill_times = max(@$this->rebill_times, $item->rebill_times);
        }
        if ($this->currency == Am_Currency::getDefault())
            $this->base_currency_multi = 1.0;
        else 
        {
            $this->base_currency_multi = $this->getDi()->currencyExchangeTable->getRate($this->currency, 
                sqlDate(!empty($this->tm_added) ? $this->tm_added : $this->getDi()->sqlDateTime ));
            if (!$this->base_currency_multi)
                $this->base_currency_multi = 1;
        }
        $this->getDi()->hook->call(Am_Event::INVOICE_CALCULATE, array('invoice' => $this));
        return $this;
    }

    /**
     * Validate invoice to make sure it is fully ready for payment processing
     * check for 
     * - no items
     * - items are compatible by its terms
     * - user_id set and valid
     * - paysys_id set and valid
     * - @todo currency set and valid
     * - trial1Total, total - calculated
     * - coupon_id is !set || valid
     * - !isPaid
     * @return null|array null if OK, array of translated errors if not
     */
    function validate()
    {
        if (!$this->getItems())
            return array(___('No items selected for purchase'));
        // @todo check compatible items
        if (empty($this->user_id))
            throw new Am_Exception_InternalError("User is not assigned to invoice in " . __METHOD__);
        if (null == $this->getUser())
            throw new Am_Exception_InternalError("Could not load invoice user in " . __METHOD__);
        if ($error = $this->validateCoupon())
            return array($error);
        if ($error = $this->checkProductRequirements())
            return $error;
    }

    /**
     * Check product requirements and return null if OK, or error message
     * @return null|string
     */
    protected function checkProductRequirements()
    {
        $activeProductIds = $expiredProductIds = array();
        if ($this->_user)
        {
            $activeProductIds = $this->_user->getActiveProductIds();
            $expiredProductIds = $this->_user->getExpiredProductIds();
        }
        $error = $this->getDi()->productTable->checkRequirements($this->getProducts(), $activeProductIds, $expiredProductIds);
        return $error ? $error : null;
    }

    /**
     * Make choice of paysystem if $this->getDi()->config->get('product_paysystem') enabled
     * If paysys_id is null, we will try to choose an acceptable paysystem automatically
     * @param string  paysys_id (optional)
     */
    protected function _choosePaysystemIfProductPaysystem($paysys_id)
    {
        $productPs = array();
        foreach ($this->getProducts() as $pr)
        {
            $ids[] = $pr->product_id;
            if ($pr->paysys_id)
                $productPs[$pr->product_id] = explode(',', $pr->paysys_id);
        }
        if (!$productPs)
            return $paysys_id;
        $productPs[-2] = array($paysys_id); // if was selected by user, choose from selected
        $intersect_paysys_id = call_user_func_array('array_intersect', $productPs);
        if (!$intersect_paysys_id)
            throw new Am_Exception_InternalError("Could not find acceptable payment processor [selected $paysys_id] for this combination of products: " . join(',', $ids));
        return current($intersect_paysys_id);
    }

    protected function _autoChoosePaysystemIfProductPaysystem()
    {
        $productPs = array();
        foreach ($this->getProducts() as $pr)
        {
            $ids[] = $pr->product_id;
            if ($pr->paysys_id)
                $productPs[$pr->product_id] = explode(',', $pr->paysys_id);
        }
        if (count($productPs) > 1)
            $intersect_paysys_id = call_user_func_array('array_intersect', $productPs);
        else
            $intersect_paysys_id = $productPs;
        foreach ($intersect_paysys_id as $paysys_id)
        {
            $ps = $this->getDi()->paysystemList->get($paysys_id);
            if (!$ps)
                continue;
            $plugin = $this->getDi()->plugins_payment->get($paysys_id);
            if (!$plugin || $err = $plugin->isNotAcceptableForInvoice($this))
                continue;
            return $paysys_id;
        }
        throw new Am_Exception_InputError("Could not find acceptable payment processor [none selected] for this combination of products: " . join(',', $ids));
    }

    /**
     * Validates and sets passed paysysy_id
     * @see $this->paysys_id
     * @param string $paysys_id
     */
    public function setPaysystem($paysys_id)
    {
        $this->paysys_id = null;
        if ($this->isZero())
        {
            $this->paysys_id = 'free';
            return $this->paysys_id;
        }
        if ($this->getDi()->config->get('product_paysystem'))
            $paysys_id = $paysys_id === null ?
                $this->_autoChoosePaysystemIfProductPaysystem() :
                $this->_choosePaysystemIfProductPaysystem($paysys_id);
        if (!$paysys_id || !$this->getDi()->paysystemList->isPublic($paysys_id))
            throw new Am_Exception_InputError(___('Please select payment system for payment'));
        if (!$plugin = $this->getDi()->plugins_payment->get($paysys_id))
            throw new Am_Exception_InternalError('Could not load paysystem ' . htmlentities($paysys_id));
        if ($err = (array)$plugin->isNotAcceptableForInvoice($this))
            throw new Am_Exception_InputError(___('Sorry, it is impossible to use this payment method for this order. Please select another payment method') . ' : ' . $err[0]);
        $this->paysys_id = $paysys_id;
        return $this->paysys_id;
    }

    /**
     * Return user record (by user_id) or null
     * caches result in $this->_user
     * @return User|null
     */
    function getUser()
    {
        if (empty($this->user_id))
            return $this->_user = null;
        if (empty($this->_user) || $this->_user->user_id != $this->user_id)
        {
            $this->_user = $this->getDi()->userTable->load($this->user_id);
        }
        return $this->_user;
    }

    function setUser(User $user)
    {
        $this->_user = $user;
        $this->user_id = $user->user_id;
    }

    /**
     * Return _coupon record (by coupon_id) or a new empty _coupon object
     * caches result in $this->_coupon
     * @return _coupon|null
     */
    function getCoupon()
    {
        if (!empty($this->coupon_id))
            if (!$this->_coupon || ($this->_coupon->coupon_id != $this->coupon_id))
                $this->_coupon = $this->getDi()->couponTable->load($this->coupon_id);
        return $this->_coupon;
    }

    /**
     * Set _coupon and check if that is acceptable
     * @param <type> $_coupon
     * @return Invoice provides fluent interface
     */
    function setCoupon(_coupon $_coupon)
    {
        $this->_couponCode = null;
        $this->coupon_id = $_coupon->coupon_id;
        $this->_coupon = $_coupon;
    }

    /**
     * Set _coupon code 
     * You also need to call validateCoupon() to get it loaded and checked
     */
    function setCouponCode($code)
    {
        $this->_coupon = $this->coupon_id = null;
        $this->_couponCode = $code;
    }

    /**
     * Validate currently set coupon code, return error message or null of OK
     * @see setCouponCode
     */
    function validateCoupon()
    {
        if ($this->_couponCode != '')
        {
            $this->_coupon = $this->getDi()->couponTable->findFirstByCode($this->_couponCode);
            if (!$this->_coupon)
                return ___('No coupons found with such coupon code');
            $this->coupon_id = $this->_coupon->coupon_id;
        }
        if (!empty($this->coupon_id) && ($error = $this->getCoupon()->validate(@$this->user_id)))
            return $error;
    }

    /**
     * @return Am_Currency
     */
    function getCurrency($value = null)
    {
        $c = new Am_Currency($this->currency);
        if ($value)
            $c->setValue($value);
        return $c;
    }

    /**
     * Return flag necessary for _coupon discount calculation
     * @todo actual calcultations in Invoice::isFirstPayment
     * @return boolean
     */
    public function isFirstPayment()
    {
        return true;
    }

    protected function _getItemCompatibleError($reasonSubstring, InvoiceItem $item, InvoiceItem $existingItem)
    {
        return sprintf('invoice_recurring_terms_incompatible_' . $reasonSubstring, $item->item_title, $existingItem->item_title);
    }

    /**
     * This checks new item for compatibility with already added products
     * in the invoice items. If settings of recurring billing is incompatible,
     * - product is not compared to itself
     * - if product has rebill_times = 0, it is compatible
     * - if product has rebill_times = 1, firstPeriod must be compatible (equal to)
     *     with all other such products in basket
     * - if product has rebill_times > 1, secondPeriod must be compatible (equal to)
     *     with all other such products in basket
     * @return null|string translated error message
     */
    public function isItemCompatible(InvoiceItem $item)
    {
        if ($item->rebill_times == 0)
            return;
        if (!$this->getItems())
            return;
        foreach ($this->getItems() as $existingItem)
        {
            if ($item === $existingItem)
                continue;
            if (0 == $existingItem->rebill_times)
                continue;
            if ($existingItem->currency != $item->currency)
                return $this->_getItemCompatibleError('CURRENCY', $item, $existingItem);
            if ($existingItem->first_period != $item->first_period)
                return $this->_getItemCompatibleError('FIRSTPERIOD', $item, $existingItem);
            if ($existingItem->rebill_times != $item->rebill_times)
                return $this->_getItemCompatibleError('REBILLTIMES', $item, $existingItem);
            if ($existingItem->rebill_times > 1)
            {
                if ($existingItem->second_period != $item->second_period)
                    return $this->_getItemCompatibleError('SECONDPERIOD', $item, $existingItem);
            }
        }
    }

    function isProductCompatible(IProduct $product)
    {
        $newItem = $this->createItem($product);
        return $this->isItemCompatible($newItem);
    }

    public function getLogin()
    {
        return $this->getUser()->login;
    }

    public function getUserId()
    {
        return (int) $this->user_id;
    }

    public function getName()
    {
        return $this->getUser()->getName();
    }

    public function getFirstName()
    {
        return $this->getUser()->name_f;
    }

    public function getLastName()
    {
        return $this->getUser()->name_l;
    }

    public function getEmail()
    {
        return $this->getUser()->email;
    }

    /// address info //////////////////
    public function getStreet()
    {
        return trim($this->getStreet1() . ' ' . $this->getStreet2());
    }

    public function getStreet1()
    {
        return $this->getUser()->street;
    }

    public function getStreet2()
    {
        return $this->getUser()->street2;
    }

    public function getCity()
    {
        return $this->getUser()->city;
    }

    public function getState()
    {
        return $this->getUser()->state;
    }

    public function getCountry()
    {
        return $this->getUser()->country;
    }

    public function getZip()
    {
        return $this->getUser()->zip;
    }

    public function getPhone()
    {
        return $this->getUser()->phone;
    }

    /// shipping address info //////////
    public function getShippingStreet()
    {
        return $this->getUser()->street;
    }

    public function getShippingCity()
    {
        return $this->getUser()->city;
    }

    public function getShippingState()
    {
        return $this->getUser()->state;
    }

    public function getShippingCountry()
    {
        return $this->getUser()->country;
    }

    public function getShippingZip()
    {
        return $this->getUser()->zip;
    }

    public function getShippingPhone()
    {
        return $this->getUser()->phone;
    }

    /**
     * Return one-line description of products in the basket
     * to be passed to payment system
     * @return string 
     */
    function getLineDescription()
    {
        $items = $this->getItems();
        if (1 == count($items))
            return current($items)->item_title;
        else
            return $this->getDi()->config->get('multi_title', 'Invoice Items (by invoice#' . $this->_getPublicId() . ')');
    }

    /**
     * Return invoice id with not-numeric random string added
     * this avoids "duplicate invoice" error in paysystems like
     * Paypal, and can be easily stipped by running
     * it is proved that in same session it will return the same id for same invoice
     * @example $cleanInvoiceId = intval($invoice->getRandomizedId());
     * @param string $param if set to date it will be not be unique within current date
     * @return string $this->invoice_id . 'randomstring';
     */
    function getRandomizedId($param = null)
    {
        if ($param == 'date') return $this->_getPublicId() . '-' . date('Ymd');
        if ($param == 'site') return $this->_getPublicId() . '-' . substr(md5(ROOT_URL), 0,6);
        return $this->_getPublicId();
    }
    
    /**
     * Calculate planned rebill date for $n rebill
     * @return string date
     */
    function calculateRebillDate($n)
    {
        if ($n > $this->rebill_times)
            throw new Am_Exception_InternalError(__METHOD__ ." call error: n[$n] > rebill_times[$this->rebill_times]");
        $date = $this->tm_started;
        if (($date == '0000-00-00 00:00:00') || !$date)
            $date = $this->getDi()->sqlDate;
        else
            $date = preg_replace('/ .+$/', '', $date);
        if ($n == 0) return $date;
        $p = new Am_Period($this->first_period);
        $date = $p->addTo($date);
        if ($n == 1) return $date;
        $p = new Am_Period($this->second_period);
        for ($i = 1; $i < $n; $i++)
            $date = $p->addTo($date);
        return $date;
    }

    protected function _getInvoiceKey()
    {
        if (empty($this->invoice_key))
            $this->invoice_key = $this->getDi()->app->generateRandomString(16);
        return $this->invoice_key;
    }
    
    protected function _getPublicId()
    {
        if (empty($this->public_id))
            $this->public_id = $this->getDi()->app->generateRandomString(5, 'QWERTYUASDFGHJKLZXCVBNM1234567890');
        return $this->public_id;
    }

    /**
     * Return unique id for invoice. With the same prefix, returned value
     * is always the same for the same invoice
     */
    function getUniqId($prefix)
    {
        return substr(sha1($prefix . $this->_getInvoiceKey()), 0, 16);
    }
    
    /**
     * Return string in form 1123-LKj3lrkjg3
     * @link InvoiceTable->findBySecureId
     */
    function getSecureId($prefix)
    {
        return $this->public_id . "-" . $this->getUniqId($prefix);
    }

    function hasShipping()
    {
        foreach ($this->getItems() as $item)
            if ($item->is_tangible)
                return true;
        return false;
    }

    public function isZero()
    {
        return (@$this->first_total == 0) && (@$this->second_total == 0);
    }

    /**
     * @return true if this invoice is acceptable for "fixed price" plugins
     * It means - one product, no taxes, no discounts, no shipping
     */
    public function isFixedPrice()
    {
        return count($this->getItems()) == 1 &&
        $this->first_subtotal == $this->first_total &&
        $this->second_subtotal == $this->second_total;
    }

    function __toString()
    {
        $ret = $this->toArray();
        $ret['_items'] = array();
        foreach ($this->_items as $item)
            $ret['_items'][] = $item->toArray();
        return print_r($ret, true);
    }
    
    function render($indent = "", InvoicePayment $payment = null)
    {
        $prefix = (!is_null($payment) && !$payment->isFirst()) ? 'second' : 'first';
        $tm_added = is_null($payment) ? $this->tm_added : $payment->dattm;

        $out  = $indent. ___("Invoice") . ' #' . $this->public_id . " / ". amDate($tm_added) . "\n";
        $out .= $indent . str_repeat('-', 70) . "\n";
        foreach ($this->getItems() as $item)
        {
            $out .= $indent . sprintf("  %-50s %3s%8.2f\n", ___($item->item_title), $this->currency, $item->{$prefix.'_total'});
        }
        $out .= $indent . str_repeat('-', 70) . "\n";
        if ($this->{$prefix.'_subtotal'} != $this->{$prefix.'_total'})
            $out .= $indent . sprintf("  %-50s %3s%8.2f\n", ___("Subtotal"), $this->currency, $this->{$prefix.'_subtotal'});
        if ($this->{$prefix.'_discount'} > 0)
            $out .= $indent . sprintf("  %-50s %3s%8.2f\n", ___("Discount"), $this->currency, $this->{$prefix.'_discount'});
        if ($this->{$prefix.'_shipping'} > 0)
            $out .= $indent . sprintf("  %-50s %3s%8.2f\n", ___("Shipping"), $this->currency, $this->{$prefix.'_shipping'});
        if ($this->{$prefix.'_tax'} > 0)
            $out .= $indent . sprintf("  %-50s %3s%8.2f\n", ___("Tax"), $this->currency, $this->{$prefix.'_tax'});
        $out .= $indent . sprintf("  %-50s %3s%8.2f\n", ___("Total"), $this->currency, $this->{$prefix.'_total'});
        $out .= $indent . str_repeat('-', 70) . "\n";
        if ($this->rebill_times)
        {
            $out .= $indent . "  " . ___($this->getTerms()) . "\n";
            $out .= $indent . str_repeat('-', 70) . "\n";
        }
        return $out;
    }
    

    function update()
    {
        $ret = parent::update();
        $ids = array();
        foreach ($this->_items as $item)
            $item->set('invoice_id', $this->invoice_id)->save();
        return $ret;
    }

    function isConfirmed(){
        return !empty($this->is_confirmed) && ($this->is_confirmed > 0);
    }
    
    function insert($reload = true)
    {
        // Set is confirmed value if it wasn't set yet;
        if(!isset($this->is_confirmed))
        {
            // If user is not approved, invoice shouldn't be approved too. 
            if(!$this->getUser()->isApproved())
                $this->is_confirmed = self::IS_CONFIRMED_WAIT_FOR_USER;
            
            if($this->getDi()->config->get('manually_approve_invoice'))
            {
                // Now check is manually_approve_invoice_products is set.
                if($this->getDi()->config->get('manually_approve_invoice_products'))
                {
                    foreach($this->getProducts() as $p)
                        if(in_array($p->product_id, $this->getDi()->config->get('manually_approve_invoice_products',array())))
                            $this->is_confirmed  = self::IS_CONFIRMED_NOT_CONFIRMED;
                }
                else
                    $this->is_confirmed = self::IS_CONFIRMED_NOT_CONFIRMED;
                    
            }
            
            // If above checks, didn't change is_confirmed status, then invoice is confirmed; 
            if(!isset($this->is_confirmed)) $this->is_confirmed = self::IS_CONFIRMED_CONFIRMED;
            
        }
            
        
        
        $this->getDi()->hook->call(Am_Event::INVOICE_BEFORE_INSERT, array('invoice' =>$this));
        
        if (empty($this->tm_added))
            $this->tm_added = sqlTime('now');
        $this->_getInvoiceKey();
        
        $maxAttempts = 20;
        for ($i = 0; $i <= $maxAttempts; $i++)
            try {
                $this->_getPublicId();
                $ret = parent::insert($reload = true);
                break;
            } catch (Am_Exception_Db_NotUnique $e) {
                if ($i >= $maxAttempts)
                    throw $e;
                $this->public_id = null;
            }
        foreach ($this->_items as $item)
            $item->set('invoice_id', $this->invoice_id)->insert();

        $this->getDi()->hook->call(Am_Event::INVOICE_AFTER_INSERT, array('invoice' =>$this));

        return $ret;
    }

    /**
     * Dangerous! Deletes all related payments from 'payments' table
     * @see InvoicePayment
     * @see InvoiceItem
     */
    function delete()
    {
        $this->getDi()->hook->call(Am_Event::INVOICE_BEFORE_DELETE, array('invoice' =>$this));
        $this->deleteFromRelatedTable('?_invoice_item');
        // $this->deleteFromRelatedTable('?_invoice_log'); // not good idea to delete
        $this->deleteFromRelatedTable('?_invoice_payment');
        $this->deleteFromRelatedTable('?_invoice_refund');
        $this->deleteFromRelatedTable('?_access');
        parent::delete();
        $this->getUser()->checkSubscriptions(true);
        $this->getDi()->hook->call(Am_Event::INVOICE_AFTER_DELETE, array('invoice' =>$this));
        return $this;
    }


    /**
    *    Send message to user after invoice will be approved
    */
    
    function sendApprovedEmail(){
        if($et = Am_Mail_Template::load('invoice_approved_user', $this->getUser()->lang))
        {
            $et->setUser($this->getUser());
            $et->setInvoice($this);
            $et->send($this->getUser());
        }
    }
    
    
    /**
    *    Send message to user and admin if invoice require manual approval
    */
    
    function sendNotApprovedEmail(){
        if($et = Am_Mail_Template::load('invoice_approval_wait_user', $this->getUser()->lang))
        {
            $et->setUser($this->getUser());
            $et->setInvoice($this);
            $et->send($this->getUser());
        }
        if($et = Am_Mail_Template::load('invoice_approval_wait_admin', $this->getUser()->lang))
        {
            $et->setUser($this->getUser());
            $et->setInvoice($this);
            $et->send(Am_Mail_Template::TO_ADMIN);
        }
    }
    
    /**
     *  Save transaction to invoice data; 
     */
    protected function saveTransaction(Am_Paysystem_Transaction_Abstract $transaction, $invoicePaymentId = null)
    {
        $saved = new Am_Paysystem_Transaction_Saved($transaction);
        $this->data()->set(self::SAVED_TRANSACTION_KEY.'-'.time().'-'.intval($invoicePaymentId), $saved)->update();
    }
    /**
     * If given $transaction was not handled yet (@see Am_Paysystem_Transaction_Abstract::getUniqId)
     * we will handle it, and add access records to amember_invoice_access table,
     * 
     * @param Am_Paysystem_Transaction_Abstract $transaction
     */
    public function addAccessPeriod(Am_Paysystem_Transaction_Interface $transaction, 
        $invoicePaymentId = null)
    {
        if(!$this->isConfirmed()){
            // If invoice is not confirmed, we just need to store transaction data somewhere and leave.
            $this->saveTransaction($transaction, $invoicePaymentId);
            if($this->is_confirmed == self::IS_CONFIRMED_NOT_CONFIRMED) $this->sendNotApprovedEmail();
            $this->updateStatus();
            return;
        }
        $records = $this->getAccessRecords();
        $isFirstPeriod = !$records;
        $transactionDate = $transaction->getTime()->format('Y-m-d');
        $count = array();

        if ($isFirstPeriod)
        {
            foreach ($this->getItems() as $item)
            {
                if ($item->rebill_times) 
                    $start = $transactionDate; // no games with recurring billing dates
                else { // for not-recurring we can be flexible
                    $ppr = $item->tryLoadProduct();
                    if ($ppr)
                        $start = $ppr->calculateStartDate($transactionDate, $this);
                    else
                        $start = $transactionDate;
                }
                $item->addAccessPeriod($isFirstPeriod, $this, $transaction, $start, $invoicePaymentId);
            }
        }
        else
        {
            $today = clone $transaction->getTime();
            $lastBegin = $lastExpire = array();
            foreach ($records as $accessRecord)
            {
                /* @var $accessRecord Access */
                if ($accessRecord->isLifetime())
                    $accessRecord->updateQuick('expire_date', $today->format('Y-m-d'));
                $lastBegin[$accessRecord->product_id] =
                    max(@$lastBegin[$accessRecord->product_id], $accessRecord->begin_date);
                $lastExpire[$accessRecord->product_id] =
                    max(@$lastExpire[$accessRecord->product_id], $accessRecord->expire_date);
                $count[$accessRecord->product_id] =
                    @$count[$accessRecord->product_id] + 1;
            }
            foreach ($this->getItems() as $item)
            {
                if ($count[$item->item_id] > $item->rebill_times) continue; // this item rebills is over
                /* @var $item InvoiceItem */
                $start = max($lastExpire[$item->item_id], $today->format('Y-m-d'));
                if ($start == Am_Period::MAX_SQL_DATE || $start == Am_Period::RECURRING_SQL_DATE)
                {
                    $start = $transactionDate;
                    $yesterday = date('Y-m-d', strtotime($start)-26*3600);
                    // set date to yesterday for past access record to this item
                    $this->getDi()->accessTable->setDateForRecurring($item, $yesterday);
                }
                $item->addAccessPeriod($isFirstPeriod, $this, $transaction, $start, $invoicePaymentId);
            }
        }
        if ($isFirstPeriod) {
            $this->updateQuick('tm_started', $transaction->getTime()->format('Y-m-d H:i:s'));
            if ($this->coupon_id)
            {
                $coupon = $this->getDi()->couponTable->load($this->coupon_id, false);
                if ($coupon) $coupon->setUsed();
            }
            $this->updateStatus();
            $this->getUser()->checkSubscriptions(true);
            $this->getDi()->hook->call(new Am_Event_InvoiceStarted(null, array(
                'user'        => $this->getUser(),
                'invoice'     => $this,
                'transaction' => $transaction,
                'payment'     => $invoicePaymentId ? 
                    $this->getDi()->invoicePaymentTable->load($invoicePaymentId, false) :
                    null
            )));
        } else {
            $this->updateStatus();
            $this->getUser()->checkSubscriptions(true);
        }
    }

    /**
     * Add small manual access period for example during cc_rebill failure
     * @param date $start
     * @param date $expire
     */
    public function extendAccessPeriod($newExpire)
    {
        // get last expiration date
        $expire = $this->getAccessExpire();
        // we will be updating only records with this expiration date
        // because all other records are already expired and we will
        // not touch it
        $count = 0;
        foreach ($this->getAccessRecords() as $accessRecord)
        {
            if ($accessRecord->expire_date != $expire)
                continue;
            $accessRecord->setDisableHooks(true);
            $accessRecord->expire_date = $newExpire;
            $accessRecord->update();
            $accessRecord->setDisableHooks(false);
            $count++;
        }
        if ($count)
            $this->getDi()->userTable->load($this->user_id)->checkSubscriptions(true);
    }

    public function addPayment(Am_Paysystem_Transaction_Abstract $transaction)
    {
        $p = $this->addPaymentWithoutAccessPeriod($transaction);
        $this->addAccessPeriod($transaction, $p->invoice_payment_id);
        $this->getDi()->hook->call(new Am_Event_PaymentWithAccessAfterInsert(null, 
            array('payment' => $p, 
                  'invoice' => $p->getInvoice(),
                  'user'    => $p->getInvoice()->getUser())));        
        return $p;
    }

    /** @return Invoice_Payment */
    public function addPaymentWithoutAccessPeriod(Am_Paysystem_Transaction_Abstract $transaction)
    {
        $c = $this->getPaymentsCount();
        if ($c >= $this->getExpectedPaymentsCount())
        {
            $rt = (int) $this->rebill_times;
            if ($this->rebill_times)
                throw new Am_Exception_Paysystem("Existing payments count [$c] exceeds number of allowed rebills [$rt]+1, could not add new payment");
            else { // if that is not a recurring transaction, it is already handled for sure
                $paysys_id = $transaction->getPaysysId();
                $transaction_id = $transaction->getUniqId();
                throw new Am_Exception_Paysystem_TransactionAlreadyHandled("Transaction {$paysys_id}-{$transaction_id} is already handled");
            }
        }
        $p = $this->getDi()->invoicePaymentRecord;
        $p->setFromTransaction($this, $transaction);
        $p->_setInvoice($this); // caching
        try
        {
            $p->insert();
        }
        catch (Am_Exception_Db_NotUnique $e)
        {
            if ($e->getTable() == '?_invoice_payment')
                throw new Am_Exception_Paysystem_TransactionAlreadyHandled("Transaction {$p->paysys_id}-{$p->transaction_id} is already handled");
            else 
                throw $e;
        }
        $this->updateRebillDate();
        $this->updateStatus();
        return $p;
    }

    /**
     * @access protected
     */
    function updateRebillDate()
    {
        $date = null;
        $c = $this->getPaymentsCount();
        if ($this->first_total <= 0)
            $c++; // first period is "fake" because it was free trial
        //if ($c < $this->getExpectedPaymentsCount()) // not yet done with rebills
        if ($this->rebill_times > ($c - 1)) // not yet done with rebills
        {
            // we count starting from first payment date, we rely on tm_started field here
            if ($this->first_total <= 0)
                list($date, ) = explode(' ', $this->tm_started);
            else
                $date = $this->getAdapter()
                    ->selectCell("SELECT MIN(dattm) FROM ?_invoice_payment WHERE invoice_id=?d", $this->invoice_id);
            $date = date('Y-m-d', strtotime($date));

            $period1 = new Am_Period($this->first_period);
            $date = $period1->addTo($date);
            $period2 = new Am_Period($this->second_period);
            for ($i = 1; $i < $c; $i++) // we skip first payment here, already added above
            {
                $date = $period2->addTo($date);
            }
        }
        $this->updateQuick('rebill_date', $date);
        $this->rebill_date = $date;
    }

    public function addVoid(Am_Paysystem_Transaction_Abstract $transaction, $origReceiptId)
    {
        return $this->addRefundInternal($transaction, $origReceiptId, InvoiceRefund::VOID);
    }

    /** Add refund for payment with receiptId === $origReceiptId and related access records */
    public function addRefund(Am_Paysystem_Transaction_Abstract $transaction, $origReceiptId, $amount = null)
    {
        return $this->addRefundInternal($transaction, $origReceiptId, InvoiceRefund::REFUND, $amount);
    }

    /** Add chargback for payment with given receiptId and disable ALL access records */
    public function addChargeback(Am_Paysystem_Transaction_Abstract $transaction, $origReceiptId)
    {
        return $this->addRefundInternal($transaction, $origReceiptId, InvoiceRefund::CHARGEBACK);
    }

    protected function addRefundInternal(Am_Paysystem_Transaction_Abstract $transaction, $origReceiptId, $refundType, $refundAmount = null)
    {
        $amount = 0.0;
        $access = $this->getAccessRecords();
        $dattm = $transaction->getTime();
        $yesterday = clone $dattm;
        $yesterday->modify('-1 days');
        foreach ($this->getDi()->invoicePaymentTable->findBy(
            array('receipt_id' => $origReceiptId, 'invoice_id' => $this->invoice_id)) as $p)
        {
            $p->refund($dattm);
            $amount += $p->amount;
//          do not disable any access for refunds
//            if ($refundType == InvoiceRefund::REFUND) // disable only related access
//                foreach ($access as $a)
//                    if ($a->invoice_payment_id == $p->invoice_payment_id)
//                        $a->updateQuick('expire_date', $yesterday->format('Y-m-d'));
        }
        //if ($refundType != InvoiceRefund::REFUND)
        $this->revokeAccess($transaction);
        if (!$refundAmount)
            $refundAmount = $amount;
        
        // Some payment system plugins can pass negative value here.
        
        $refundAmount = abs($refundAmount);
        
        $r = $this->getDi()->invoiceRefundRecord;
        $r->setFromTransaction($this, $transaction, $origReceiptId, InvoiceRefund::REFUND);
        $r->amount = $refundAmount;
        $r->refund_type = (int)$refundType;
        if (!empty($p))
            $r->invoice_payment_id = $p->invoice_payment_id;
        $r->insert();
        
        $this->updateStatus();
        $this->getUser()->checkSubscriptions(true);
    }
    function emailCanceled()
    {
        $products = $this->getProducts();
        if($this->getDi()->config->get('mail_cancel_member',0))
        {
            $et = Am_Mail_Template::load('mail_cancel_member');
            if (!$et)
                throw new Am_Exception_Configuration("No e-mail template found for [mail_cancel_member]");
            $et->setUser($this->getUser());
            $et->setProduct($products[0]);
            $et->setInvoice($this);
            $et->send($this->getUser()->getEmail());
        }
        if($this->getDi()->config->get('mail_cancel_admin',0))
        {
            $et = Am_Mail_Template::load('mail_cancel_admin');
            if (!$et)
                throw new Am_Exception_Configuration("No e-mail template found for [mail_cancel_admin]");
            $et->setUser($this->getUser());
            $et->setProduct($products[0]);
            $et->setInvoice($this);
            $et->sendAdmin();
        }
    }

    public function updateStatus()
    {
        $saved = $this->status;
        $this->status = self::PENDING;
        
        if(!$this->isConfirmed()) 
            $this->status = self::NOT_CONFIRMED;
        else do { 
            $row = $this->getTable()->getAdapter()->selectRow("
                SELECT 
                    (SELECT COUNT(*) FROM ?_invoice_payment p WHERE p.invoice_id=?d and p.amount>0) as payments,
                    (SELECT COUNT(*) FROM ?_invoice_refund p WHERE p.invoice_id=?d and p.refund_type=1) as chargebacks,
                    (SELECT COUNT(*) FROM ?_invoice_refund p WHERE p.invoice_id=?d and p.refund_type<>1) as refunds,
                    (SELECT COUNT(*) FROM ?_access p WHERE p.invoice_id=?d ) as access
                ", $this->invoice_id, $this->invoice_id, $this->invoice_id, $this->invoice_id
            );
            if ($row['chargebacks']) 
            {
                $this->status = self::CHARGEBACK;
                break;
            }
            if ( $row['access']  || $row['payments'] )
            {
                if (!$this->rebill_times) {
                    $this->status = self::PAID;
                } elseif ($row['payments'] >= $this->getExpectedPaymentsCount()) {
                    $this->status = self::RECURRING_FINISHED;
                } elseif ($this->tm_cancelled) {
                    $this->status = self::RECURRING_CANCELLED;
                } else {
                    $this->status = self::RECURRING_ACTIVE;
                }
                break;
            }
        } while (false);
        if ($saved != $this->status)
        {
            if($this->status == self::RECURRING_CANCELLED)
                $this->emailCanceled();
            $this->updateSelectedFields('status');
            $this->getDi()->hook->call(Am_Event::INVOICE_STATUS_CHANGE, array(
                'invoice' => $this,
                'status'  => $this->status,
                'oldStatus' => $saved,
            ));
        }
    }
    
    /**
     * How many payments must be done here for complete cycle
     */
    public function getExpectedPaymentsCount()
    {
        $ret = 0;
        if ($this->first_total > 0) $ret++;
        if ($this->second_total > 0) $ret += $this->rebill_times;
        return $ret;
    }

    public function getStatus()
    {
        return $this->status;
    }
    
    public function getStatusTextColor()
    {
        $color = "";
        switch($this->status){
            case self::PAID :
            case self::RECURRING_ACTIVE : 
                $color = "green"; 
                break;
            case self::CHARGEBACK : 
            case self::RECURRING_CANCELLED : 
            case self::RECURRING_FAILED : 
            case self::NOT_CONFIRMED : 
                $color="red";
                break;
        }
        return empty($color) ? ___(self::$statusText[$this->status]) : "<font color=$color>".___(self::$statusText[$this->status])."</font>";
    }
    
    public function getStatusText()
    {
        return ___(self::$statusText[$this->status]);
    }

    public function stopAccess(Am_Paysystem_Transaction_Abstract $transaction)
    {
        // if second period has been set to lifetime
        // check if we've received all expected payments and invoice is not cancelled
        // if so, do not stop access
        if (($this->second_period == Am_Period::MAX_SQL_DATE)
            && ($this->status != Invoice::RECURRING_CANCELLED)
            && ($this->getPaymentsCount() >= $this->getExpectedPaymentsCount()))
        {
            return;
        }
        // stop access by setting expiration date to yesterday
        $yesterday = clone $transaction->getTime();
        $yesterday->modify('-1 days');
        $date = $yesterday->format('Y-m-d');
        foreach ($this->getAccessRecords() as $accessRecord)
        {
            if ($accessRecord->expire_date > $date)
                $accessRecord->updateQuick('expire_date', $date);
        }
        $this->getUser()->checkSubscriptions(true);
        $this->updateStatus();
    }
    public function revokeAccess(Am_Paysystem_Transaction_Abstract $transaction)
    {
        $yesterday = clone $transaction->getTime();
        $yesterday->modify('-1 days');
        $date = $yesterday->format('Y-m-d');
        foreach ($this->getAccessRecords() as $accessRecord)
        {
            if ($accessRecord->expire_date > $date)
                $accessRecord->updateQuick('expire_date', $date);
        }
        $this->getUser()->checkSubscriptions(true);
        $this->updateStatus();
    }

    /**
     * @return array of related Access objects
     */
    public function getAccessRecords()
    {
        return $this->getDi()->accessTable->findByInvoiceId($this->invoice_id, null, null, "access_id");
    }

    /** @return date max expiration date of current invoice's access records */
    public function getAccessExpire()
    {
        return $this->_db->selectCell("SELECT MAX(expire_date) FROM ?_access
            WHERE invoice_id=?d", $this->invoice_id);
    }

    public function getPaymentRecords()
    {
        return $this->getDi()->invoicePaymentTable->findByInvoiceId($this->invoice_id, null, null, "invoice_payment_id");
    }

    public function getRefundRecords()
    {
        return $this->getDi()->invoiceRefundTable->findByInvoiceId($this->invoice_id, null, null, "invoice_refund_id");
    }

    public function getPaymentsCount()
    {
        return $this->getDi()->invoicePaymentTable->getPaymentsCount($this->invoice_id);
    }

    public function setCancelled($cancelled = true)
    {
        $this->updateQuick(array(
            'tm_cancelled' => $cancelled ? sqlTime('now') : null,
            'rebill_date' => null,
        ));
        if (!$cancelled)
            $this->updateRebillDate();
        $this->updateStatus();
        return $this;
    }

    public function isCancelled()
    {
        if ($this->tm_cancelled == '0000-00-00 00:00:00')
            $this->tm_cancelled = null;
        return (bool) $this->tm_cancelled;
    }

    /**
     * @return bool true if there was real payments for this invoice
     */
    public function isPaid()
    {
        return (bool) $this->getPaymentsCount();
    }

    /**
     * @return bool true if this invoice is "completed" as it said in aMember<=3
     */
    public function isCompleted()
    {
        if (empty($this->invoice_id))
            return false;
        return (bool) $this->getAdapter()->selectCell("SELECT COUNT(*) FROM ?_access WHERE invoice_id=?d", $this->invoice_id);
    }
    
    /** @return string caclulated billing terms */
    public function getTerms()
    {
        $tt = new Am_TermsText($this);
        return (string)$tt;
    }

    public function __sleep()
    {
        return array_merge(parent::__sleep(), array('_items'));
    }

    public function __wakeup()
    {
        parent::__wakeup();
    }
    
    public function exportXmlLog()
    {
        $xml = new XMLWriter();
        $xml->openMemory();
        $xml->setIndent(true);
        $xml->startDocument();
        $xml->startElement('invoice-log');
        $xml->writeElement('version', '1.0'); // log format version
        $xml->startElement('config');
        $xml->startElement('item');$xml->writeAttribute('name', 'plugin.'.$this->paysys_id.'.sample');$xml->text('VALUE');$xml->endElement();
        $xml->endElement();
        $xml->writeComment(sprintf("Dumping invoice#%d, user#%d", $this->invoice_id, $this->user_id));
      
        $xml->startElement('event');
        $xml->writeAttribute('time', $this->tm_added);
        $this->exportXml($xml, 
            array('element'=>'invoice', 
                  'nested' => array(
                      array('invoiceItem'), 
                      array('access', array('element' => 'access')), 
                      array('invoicePayment', array('element' => 'invoice-payment')),
                  )));
        $xml->endElement();
        
        foreach ($this->getDi()->invoiceLogTable->findByInvoiceId($this->pk()) as $log)
        {
            $xml->startElement('event');
            $xml->writeAttribute('time', $log->tm);
            foreach ($log->getXmlDetails() as $a)
            {
                list($type, $source) = $a;
                $xml->writeRaw($source);
            }
            $xml->endElement();
        }
        
        $xml->endElement();
        echo $xml->flush();
    }

}

/**
 * @method InvoiceTable getInstance() 
 * @method Invoice[] selectObjects()
 */
class InvoiceTable extends Am_Table_WithData
{

    protected $_key = 'invoice_id';
    protected $_table = '?_invoice';
    protected $_recordClass = 'Invoice';

    function findPaidCountByCouponId($coupon_id, $user_id)
    {
        return $this->_db->selectCell("
            SELECT COUNT(*) 
            FROM ?_invoice
            WHERE coupon_id=?d
            AND user_id=?d
            AND status<>?d
        ", $coupon_id, $user_id, Invoice::PENDING);
    }

    function findForRebill($date, $paysys_id = null)
    {
        return $this->selectObjects("
            SELECT * FROM ?_invoice
            WHERE rebill_date = ? AND IFNULL(is_cancelled,0)=0 { AND paysys_id = ? }", $date, $paysys_id ? $paysys_id : DBSIMPLE_SKIP);
    }

    /** @return Invoice|null */
    function findByReceiptIdAndPlugin($receiptId, $paysysId)
    {
        $objs = $this->selectObjects("SELECT i.* FROM ?_invoice i
            LEFT JOIN ?_invoice_payment p
            ON p.invoice_id=i.invoice_id
            WHERE p.receipt_id=?
            AND i.paysys_id=?", $receiptId,  $paysysId);

        return count($objs) ? current($objs) : null;
    }
    
    /** @return Invoice|null */
    function findBySecureId($invoiceId, $prefix)
    {
        @list($id, $code) = explode('-', filterId($invoiceId), 2);
        $id = filterId($id);
        if (!strlen($id)) return;
        $invoice = $this->findFirstByPublicId($id);
        if (!$invoice) return;
        if ($invoice->getUniqId($prefix) != $code) return;
        return $invoice;
    }
    
    // We are doing it with plain SQL, it is potentially a trouble
    // but does not kill server
    public function clearPending($date)
    {
        $ids = $this->_db->selectCol("SELECT i.invoice_id 
            FROM ?_invoice i
                LEFT JOIN ?_invoice_payment p ON p.invoice_id = i.invoice_id 
                LEFT JOIN ?_access a ON a.invoice_id = i.invoice_id
            WHERE i.status = 0 AND p.invoice_payment_id IS NULL AND a.access_id IS NULL
             AND i.tm_added < ?
            GROUP BY i.invoice_id
            ", sqlTime($date));
        if (!$ids) return;
        $tables = array('?_invoice', '?_invoice_item', '?_invoice_log', '?_invoice_refund');
        foreach ($tables as $t)
            $this->_db->query("DELETE FROM $t WHERE invoice_id IN (?a)", $ids);
        $this->_db->query("DELETE FROM ?_data WHERE `table`='invoice_id' AND `id` IN (?a)", $ids);
        return count($ids);
    }

}
